set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/* *******************************************************************
** DATE :
** Writer :
** Memo :
******************************************************************** */
CREATE PROCEDURE [dbo].[TSP_Event_GetList]
(
	@IsTransaction	tinyint			= 0
,	@ErrorCode		int				= 0		OUTPUT
,	@ErrorMessage	nvarchar( 1000 )= ''	OUTPUT

,	@_isCurrentOnly	bit = 1
,	@isShow			bit = null
,	@PageNo	int	= 1	
,	@PageSize	tinyint	= 10	
,	@CntRow	int	= NULL	OUTPUT
,	@CntTotal	int	= NULL	OUTPUT
)
as
Set NOCOUNT ON

-- Frk Default Value
DECLARE @FrkDateNow DateTime
DECLARE @FrkRowCount int
DECLARE @FrkTranCount int
DECLARE @FrkErrorCode int
DECLARE @FrkErrorMessage nvarchar( 1000 )
Set @FrkDateNow = GetDate()
set @FrkTranCount = @@TRANCOUNT
-- Frk Check


If ( @IsTransaction = 0 )
Begin
	If ( @FrkTranCount < 1 )
	Begin
		Set @FrkErrorCode = -1
		Set @FrkErrorMessage = 'Transaction.'
		goto ERROR
	End
	Else If ( @FrkTranCount > 1 )
	Begin
		Set @FrkErrorCode = -2
		Set @FrkErrorMessage = 'Transaction.'
		goto ERROR
	End
End
Else
Begin
	If ( @FrkTranCount <> 0 )
	Begin
		Set @FrkErrorCode = -3
		Set @FrkErrorMessage = 'Transaction.'
		goto ERROR
	End
	Begin Tran
End

-- Implementation
Select
	@Cnttotal = count(*)
FROM
	dbo.TB_Event with ( nolock )
Where
	1 = case 
			when @_isCurrentOnly = 1 and 
				dtEventStart <= @FrkDateNow and 
				dtEventEnd > @FrkDateNow 
			then 1
			when @_isCurrentOnly = 0 then 1
			else 0
		end
and	isShow = isnull(@isShow, isShow)
DECLARE @SkipPage INT
SET @SkipPage = ( ( @PageNo - 1 ) * @PageSize )

IF ( @PageNo = 1 )
BEGIN
	SELECT	Top( @PageSize )
		n4EventSN
	,	strEventStart
	,	strEventEnd
	,	strPublicationDate
	,	strImageUrl
	,	strBigImageUrl
	,	strTitle
	,	strContent
	,	isShow
	,	n4TotalCommentCount
	,	n4ParticipationCount
	,	n4UniqueUserCount
	,	dtCreateDate
	,	strManagerName
	,	strBigImageUrl2
	,	strBigImageUrl3
	,	strBigImageUrl4
	,	strBigImageUrl5
	,	strBigImageUrl6
	,	strBigImageUrl7
	,	strBigImageUrl8
	,	strBigImageUrl9
	,	strBigImageUrl10
	,	CodeContent
	FROM
		dbo.TB_Event with ( nolock )
	Where
		1 = case 
				when @_isCurrentOnly = 1 and 
					dtEventStart <= @FrkDateNow and 
					dtEventEnd > @FrkDateNow 
				then 1
				when @_isCurrentOnly = 0 then 1
				else 0
			end
	and	isShow = isnull(@isShow, isShow)
	ORDER BY
		n4EventSN DESC
END
ELSE
BEGIN
	SELECT	Top( @PageSize )
		n4EventSN
	,	strEventStart
	,	strEventEnd
	,	strPublicationDate
	,	strImageUrl
	,	strBigImageUrl
	,	strTitle
	,	strContent
	,	isShow
	,	n4TotalCommentCount
	,	n4ParticipationCount
	,	n4UniqueUserCount
	,	dtCreateDate
	,	strManagerName
	,	strBigImageUrl2
	,	strBigImageUrl3
	,	strBigImageUrl4
	,	strBigImageUrl5
	,	strBigImageUrl6
	,	strBigImageUrl7
	,	strBigImageUrl8
	,	strBigImageUrl9
	,	strBigImageUrl10
	,	CodeContent
	FROM
	(
		SELECT
			n4EventSN
		,	strEventStart
		,	strEventEnd
		,	strPublicationDate
		,	strImageUrl
		,	strBigImageUrl
		,	strTitle
		,	strContent
		,	isShow
		,	n4TotalCommentCount
		,	n4ParticipationCount
		,	n4UniqueUserCount
		,	dtCreateDate
		,	strManagerName
		,	strBigImageUrl2
		,	strBigImageUrl3
		,	strBigImageUrl4
		,	strBigImageUrl5
		,	strBigImageUrl6
		,	strBigImageUrl7
		,	strBigImageUrl8
		,	strBigImageUrl9
		,	strBigImageUrl10
		,	CodeContent
		,	ROW_NUMBER() OVER ( ORDER BY
			n4EventSN DESC
		) AS ROWNUM
		FROM
			dbo.TB_Event with ( nolock )
		Where
			1 = case 
					when @_isCurrentOnly = 1 and 
						dtEventStart <= @FrkDateNow and 
						dtEventEnd > @FrkDateNow 
					then 1
					when @_isCurrentOnly = 0 then 1
					else 0
				end
		and	isShow = isnull(@isShow, isShow)
	)	A
	WHERE
		ROWNUM > @SkipPage
	AND	ROWNUM <= @SkipPage + @PageSize
	
END



-- Frk Error Check
SELECT
	@FrkErrorCode = @@ERROR
,	@FrkRowCount = @@RowCount

-- End

COMPLETE:
	If ( @IsTransaction <> 0 )
	Begin
		If ( @@TRANCOUNT = 1 )
		Begin
			Commit Tran
		End
	End
	SET @ErrorCode = 0
	SET @ErrorMessage = ''
	return 0
ERROR:
	If ( @IsTransaction <> 0 )
	Begin
		If ( @@TRANCOUNT = 1 )
		Begin
			Rollback Tran
		End
	End
	SET @ErrorCode = @FrkErrorCode
	SET @ErrorMessage = @FrkErrorMessage
	return @FrkErrorCode
